-- @owner: cr13
-- @date: 2025/3/27
-- @testpoint:函数存在嵌套关系，视图引用函数，部分合理报错
--step1:建表;expect:成功
drop table if exists t_ddl0029;
CREATE TABLE t_ddl0029 (
 employee_id serial PRIMARY KEY,
 employee_name varchar(50) NOT NULL,
 department varchar(50),
 performance_score numeric(5, 2),
 hire_date date
);
insert into t_ddl0029(employee_name,department,performance_score,hire_date)
values('LILI','sales',95.95,'2025-01-07');
--step2:创建自定义函数;expect:成功
drop function if exists func_ddl0029;
CREATE FUNCTION func_ddl0029(p_hire_date date) RETURNS integer
AS $$
DECLARE
 v_working_years integer;
 v_current_date date := current_date;
BEGIN
 v_working_years := extract(year from age(v_current_date, p_hire_date));
 RETURN v_working_years;
END;
$$ LANGUAGE plpgsql;
/
--step3:创建自定义函数，嵌套函数1;expect:成功
drop function if exists func_ddl0029_01;
CREATE FUNCTION func_ddl0029_01(p_employee_id integer) RETURNS numeric(5, 2)
AS $$
DECLARE
 v_performance_score numeric(5, 2);
 v_working_years integer;
 v_comprehensive_score numeric(5, 2);
BEGIN

 SELECT performance_score INTO v_performance_score
 FROM t_ddl0029
 WHERE employee_id = p_employee_id;

 v_working_years := func_ddl0029((SELECT hire_date FROM t_ddl0029 WHERE employee_id = p_employee_id));

 v_comprehensive_score := v_performance_score * 0.6 + v_working_years * 0.4;
 RETURN v_comprehensive_score;
END;
$$ LANGUAGE plpgsql;
/
--step4:创建视图，使用上述2个函数;expect:成功
drop VIEW if exists view_ddl0029_01;
CREATE VIEW view_ddl0029_01 AS
SELECT employee_name,
 func_ddl0029(hire_date) AS working_years,
 performance_score,
 func_ddl0029_01(employee_id) AS comprehensive_score
FROM t_ddl0029;
--step5:删除函数1;expect:成功
drop function func_ddl0029;
--step6:查询视图;expect:报错
select * from view_ddl0029_01;
--step7:查询视图状态;expect:无效
select valid from pg_object where object_oid='view_ddl0029_01'::regclass;
--step8:查询视图定义;expect:有告警
select pg_get_viewdef('view_ddl0029_01');
--step9:重建函数1;expect:成功
CREATE FUNCTION func_ddl0029(p_hire_date date) RETURNS integer
AS $$
DECLARE
 v_working_years integer;
 v_current_date date := current_date;
BEGIN
 v_working_years := extract(year from age(v_current_date, p_hire_date));
 RETURN v_working_years;
END;
$$ LANGUAGE plpgsql;
/
--step10:查询视图;expect:成功
select * from view_ddl0029_01;
--step11:查询视图状态;expect:有效
select valid from pg_object where object_oid='view_ddl0029_01'::regclass;
--step12:查询视图定义;expect:成功
select pg_get_viewdef('view_ddl0029_01');

--step13:删除自定义函数2;expect:成功
drop function func_ddl0029_01;
--step14:查询视图;expect:报错
select * from view_ddl0029_01;
--step15:查询视图状态;expect:无效
select valid from pg_object where object_oid='view_ddl0029_01'::regclass;
--step16:查询视图定义;expect:有告警
select pg_get_viewdef('view_ddl0029_01');
--step17:重建函数2;expect:成功
CREATE FUNCTION func_ddl0029_01(p_employee_id integer) RETURNS numeric(5, 2)
AS $$
DECLARE
 v_performance_score numeric(5, 2);
 v_working_years integer;
 v_comprehensive_score numeric(5, 2);
BEGIN

 SELECT performance_score INTO v_performance_score
 FROM t_ddl0029
 WHERE employee_id = p_employee_id;

 v_working_years := func_ddl0029((SELECT hire_date FROM t_ddl0029 WHERE employee_id = p_employee_id));

 v_comprehensive_score := v_performance_score * 0.6 + v_working_years * 0.4;
 RETURN v_comprehensive_score;
END;
$$ LANGUAGE plpgsql;
/
--step18:查询视图;expect:成功
select * from view_ddl0029_01;
--step19:查询视图状态;expect:有效
select valid from pg_object where object_oid='view_ddl0029_01'::regclass;
--step20:查询视图定义;expect:成功
select pg_get_viewdef('view_ddl0029_01');
--step21:清理环境;expect:成功
drop table if exists t_ddl0029;
drop function if exists func_ddl0029;
drop function if exists func_ddl0029_01;
drop VIEW if exists view_ddl0029_01;